package com.excel.excelUtil;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取excel工具类
 * @author zhuangqi
 * @@date 2017/3/17.
 */
public class ReadExcelUtil {


    /**
     * excel2Map BY 行
     * @param clazz             excelBean的class
     * @param excelUrl          excel文件路径
     * @param startParameter    开始字段
     * @param endParameter      结束字段
     */
    public static <T> T excel2MapByRow(Class<T> clazz, String excelUrl, String startParameter, String endParameter)
                                            throws IOException, InvalidFormatException,
                                                NoSuchMethodException, InstantiationException,
                                                    IllegalAccessException, InvocationTargetException {

        if ("".equals(startParameter) || startParameter==null)
            throw new RuntimeException("开始字段不能为空");
        Map<String,String> nexus = excelBean2nexusMap(clazz);
        Map<String,String> resultMap = new HashMap<String, String>();
        //创建要读入的文件的输入流
        InputStream inp = null;
        inp = new FileInputStream(excelUrl);
        //根据上述创建的输入流 创建工作簿对象
        Workbook wb = null;
        wb = WorkbookFactory.create(inp);
        //得到第一页 sheet
        Sheet sheet = wb.getSheetAt(0);
        //获取信号
        boolean getStartFlag = false;
        //是否获取
        boolean getFlag = false;
        //对应的字段
        String field = null;
        for (Row row : sheet) { //遍历行

            for (Cell cell : row) { //遍历方格(一行中的列)
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if (startParameter!=null && startParameter.equals(cell.toString())){ //开始
                    getStartFlag = true;
                    continue;
                }
                if (endParameter!=null && endParameter.equals(cell.toString())){ //结束
                    return map2Bean(clazz,resultMap);
                }
                if (getStartFlag && nexus.containsKey(cell.toString())){   //存在
                    getFlag = true;
                    field = nexus.get(cell.toString());
                    continue;
                }
                if (getFlag && field!=null){    //写入
                    resultMap.put(field,cell.toString());
                    getFlag = false;
                    field = null;
                }
            }
        }

        return map2Bean(clazz,resultMap);
    }


    /**
     * excel2Map BY 列
     * @param excelUrl
     * @param startParameter
     * @param endParameter
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static <T> List<T> excel2MapByColumn(Class<T> clazz, String excelUrl, String startParameter, String endParameter)
                                                    throws IOException, InvalidFormatException,
                                                        NoSuchMethodException, InstantiationException,
                                                            IllegalAccessException, InvocationTargetException {
        List<T> resultList = new ArrayList<T>();
        Map<String,String> nexus = excelBean2nexusMap(clazz);
        Map<String,String> resultMap = new HashMap<String, String>();
        //创建要读入的文件的输入流
        InputStream inp = null;
        inp = new FileInputStream(excelUrl);
        //根据上述创建的输入流 创建工作簿对象
        Workbook wb = null;
        wb = WorkbookFactory.create(inp);

        //得到第一页 sheet
        Sheet sheet = wb.getSheetAt(0);
        //获取信号
        boolean getStartFlag = false;

        Map<Integer,String> getMap = new HashMap<Integer, String>();

        for (Row row : sheet) { //遍历行
            Map<String,String> thisData = new HashMap<String, String>();

            for (Cell cell : row) { //遍历方格(一行中的列)
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                if (startParameter!=null && startParameter.equals(cell.toString())){ //开始
                    getStartFlag = true;
                    continue;
                }
                if (endParameter !=null && endParameter.equals(cell.toString())){ //结束
                    return resultList;
                }
                if (getStartFlag && nexus.containsKey(cell.toString())){   //存在
                    getMap.put(cell.getColumnIndex(),nexus.get(cell.toString()));
                    continue;
                }
                if (getMap.size()>0){
                    if (getMap.containsKey(cell.getColumnIndex()) && !"".equals(cell.toString())){ //写入
                        thisData.put(getMap.get(cell.getColumnIndex()).toString(),cell.toString());
                    }
                }
            }

            if (thisData.size()>0){
                resultList.add((T) map2Bean(clazz,thisData));
            }
        }
        return resultList;
    }


    /**
     * ExcelBean2Map
     * @param clazz
     * @return
     */
    private static Map<String,String> excelBean2nexusMap(Class clazz) {
        Map<String,String> nexus = new HashMap<String, String>();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            Annotation annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation instanceof ExcelColumn){
                ExcelColumn excelColumn = (ExcelColumn) annotation;
                nexus.put(excelColumn.cellName(),field.getName());
            }
        }
        return nexus;
    }

    /**
     * Map2Bean
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> T map2Bean(Class clazz,Map<String,String> map) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, InstantiationException {
        T bean = (T) clazz.newInstance();
        Method[] methods = clazz.getMethods();
        for (Method method : methods){
            if (method.getName().startsWith("set")){
                String methodName = method.getName().substring(3);
                String fileName = methodName.substring(0,1).toLowerCase()+methodName.substring(1);
                if (map.containsKey(fileName)){
                    if (!method.getParameterTypes().equals(String.class.getName())){
                        throw new RuntimeException("EXCEL BEAN SET 方法参数必须为String类型");
                    }
                    Method objectSet = clazz.getDeclaredMethod(method.getName(),String.class);
                    objectSet.invoke(bean,map.get(fileName));
                }
            }
        }
        return bean;
    }

}
